//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================


namespace Shp.Runtime.OfflineSitka
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Text.RegularExpressions;
    using Shp.Runtime.Sitka.ServiceGateway.SitkaClient;

    internal class OfflineSitkaSql
    {
        public static Scope CreateContainer(ref Scope scope, Entity entity)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");

            string insertSQL = "INSERT INTO CONTAINERS (containerID) VALUES (" +
                                                  @"'" + entity.Id + "')";

            db.ExecuteNonQuery(CommandType.Text, insertSQL);
            return scope;
        }

        public static Scope CreateEntity(ref Scope scope, Entity entity)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");

            string xmlEntity = OfflineSitkaHelper.SerializeEntity(entity);
            string insertSQL = "INSERT INTO DATA (containerID, id, version, kind, entity) VALUES (" +
                                                  @"'" + scope.ContainerId + "','" + entity.Id + "', 1, '" + entity.Kind + "','" + xmlEntity + "')";

            db.ExecuteNonQuery(CommandType.Text, insertSQL);
            return scope;
        }

        public static Entity GetEntity(Scope scope)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");
            Entity entity = null;

            string selectSQL = "SELECT id, entity, version, kind FROM DATA WHERE containerId='" + scope.ContainerId + "' AND id='" + scope.EntityId + "'";

            using (SqlDataReader rdr = (SqlDataReader)db.ExecuteReader(CommandType.Text, selectSQL))
            {
                if (rdr.Read())
                {
                    entity = CreateEntity(rdr);
                }
            }

            return entity;
        }

        public static Entity UpdateEntity(Scope scope, Entity entity)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");
            Entity e = GetEntity(scope);

            string updateSQL = "UPDATE DATA SET entity ='" + OfflineSitkaHelper.SerializeEntity(entity) +
                               "', version=" + (e.Version + 1) + ", kind='" + entity.Kind + "' " +
                               " WHERE containerId='" + scope.ContainerId + "' AND id='" + entity.Id + "'";
            
            db.ExecuteNonQuery(CommandType.Text, updateSQL);
            return GetEntity(scope);
        }

        internal static void DeleteContainer(string containerId)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");
            string deleteEntitiesSQL = "DELETE FROM DATA WHERE containerId='" + containerId + "'";
            db.ExecuteNonQuery(CommandType.Text, deleteEntitiesSQL);

            string deleteContainerSQL = "DELETE FROM CONTAINERS WHERE containerId='" + containerId + "'";
            db.ExecuteNonQuery(CommandType.Text, deleteContainerSQL);
        }

        internal static void DeleteEntity(string containerId, string entityId)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");
            string deleteSQL = "DELETE FROM DATA WHERE containerId='" + containerId + "' AND id='" + entityId + "'";
            db.ExecuteNonQuery(CommandType.Text, deleteSQL);
        }

        internal static int GetContainer(string containerId)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");
            string selectSQL = "SELECT COUNT(*) FROM CONTAINERS WHERE containerId='" + containerId + "'";
            return (int) db.ExecuteScalar(CommandType.Text, selectSQL);   
        }

        internal static List<Entity> QueryContainer(Scope scope, string query)
        {
            Database db = DatabaseFactory.CreateDatabase("OfflineSitka");
            
            string selectSQL = SitkaToSqlQuery.SqlQuery(query, scope.ContainerId, scope.EntityId);

            Debug.WriteLine(selectSQL);

            using (SqlDataReader rdr = (SqlDataReader)db.ExecuteReader(CommandType.Text, selectSQL))
            {
                List<Entity> l = new List<Entity>();
                while (rdr.Read())
                {
                    Entity e = CreateEntity(rdr);
                    l.Add(e);
                }

                return l;
            }
        }

        /// <summary>
        /// Recreates an Entity from a SqlReader
        /// </summary>
        /// <param name="rdr"></param>
        /// <returns></returns>
        private static Entity CreateEntity(SqlDataReader rdr)
        {
            Entity e = OfflineSitkaHelper.DeserializeEntity(rdr["entity"].ToString());
            e.Version = (int)rdr["version"];
            e.Kind = (string)rdr["kind"];
            e.Id = (string)rdr["id"];

            return e;
        }
    }
}
